/*********************************************************************************
 *      Copyright:  (C) 2024 LingYun<iot25@lingyun>
 *                  All rights reserved.
 *
 *       Filename:  sqlite_database.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(08/12/24)
 *         Author:  LingYun <iot25@lingyun>
 *      ChangeLog:  1, Release initial version on "08/12/24 14:50:52"
 *                 
 ********************************************************************************/
#include <stdio.h>
#include <sqlite3.h>
#include <errno.h>
#include <string.h>
#include <ctype.h>

#include "sqlite_database.h"
#include "log_system.h"

//创建数据库和表
int create_db_and_table(sqlite3 *db)
{
	char		*err_msg = NULL;
	int			rc;
	const char	*sql_create_table = "CREATE TABLE IF NOT EXISTS SensorData ("
                                   "id INTEGER PRIMARY KEY, "
                                   "raw_data TEXT NOT NULL, "
                                   "sent INTEGER DEFAULT 0);";

	rc = sqlite3_exec(db,sql_create_table,0,0,&err_msg);
	if(rc != SQLITE_OK )
	{
		sqlite3_free(err_msg);
		return rc;
	}
	log_write(LOG_INFO,__FILE__,__func__,__LINE__,"Table create sucessufully or already exits");

	return SQLITE_OK;
}

//插入数据
int insert_data(sqlite3 *db, const char *buf)
{
    char *err_msg = NULL;
    char *sql;
    int rc;

	// 使用 sqlite3_mprintf 格式化 SQL 查询并转义字符串
    sql = sqlite3_mprintf("INSERT INTO SensorData (raw_data) VALUES (%Q);", buf); // %Q 会自动转义字符串中的特殊字符

    rc = sqlite3_exec(db, sql, 0, 0, &err_msg);
    if (rc != SQLITE_OK)
    {
        log_write(LOG_ERROR, __FILE__, __func__, __LINE__, "SQL error (insert data): %s", err_msg);
        sqlite3_free(err_msg);
        return rc;
    }

    //log_write(LOG_INFO, __FILE__, __func__, __LINE__, "Record inserted successfully, data sql: %s, buf: %s", sql, buf);

    return SQLITE_OK;
}


//查询数据
int select_data(sqlite3 *db,char *buf)
{
	char	*err_msg = NULL;
	char	*sql = "SELECT raw_data FROM SensorData LIMIT 1;";
	int		rc;

	memset(buf,0,1024);

	rc = sqlite3_exec(db,sql,callback,buf,&err_msg);
	if(rc != SQLITE_OK )
	{
		log_write(LOG_ERROR,__FILE__,__func__,__LINE__,"SQL error (select data):%s",err_msg);
		sqlite3_free(err_msg);
		return rc;
	}
	log_write(LOG_INFO,__FILE__,__func__,__LINE__,"Selected data successfully,data sql:%s",buf);
	return SQLITE_OK;
}

//更新数据
int updata_data(sqlite3 *db,const char *buf)
{
	char	*err_msg = NULL;
	char	sql[1024];
	int		rc;
	float	temperature;
	char	time_buf[80];

	// 解析 buf
    if(sscanf(buf, "temperature:%f,time:%79[^\n]", &temperature, time_buf) == 2) 
	{
        // 清理时间字段
        //clean_time(time_buf);

        snprintf(sql, sizeof(sql), "UPDATE SensorData SET temperature = %f, time = '%s' WHERE temperature = %.6f AND time = '%s';",temperature, time_buf, temperature, time_buf);

        rc = sqlite3_exec(db, sql, 0, 0, &err_msg);
        if (rc != SQLITE_OK) 
		{
			log_write(LOG_ERROR,__FILE__,__func__,__LINE__,"SQL error (update data):%s",err_msg);
            sqlite3_free(err_msg);
            return rc;
        }
        log_write(LOG_INFO,__FILE__,__func__,__LINE__,"Record updated successfully.\n");
    } 
	else 
	{
        log_write(LOG_ERROR,__FILE__,__func__,__LINE__,"Error parsing buff: %s", buf);
    }
	return SQLITE_OK;
}

// 删除数据
int delete_data(sqlite3 *db, const char *buf)
{
    char *err_msg;
    char *sql;
    int rc;

	//使用 sqlite3_mprintf 格式化 SQL 查询
    sql = sqlite3_mprintf("DELETE FROM SensorData WHERE raw_data = %Q;", buf); // %Q 自动转义字符串中的特殊字符

    rc = sqlite3_exec(db, sql, 0, 0, &err_msg);
    if (rc != SQLITE_OK)
    {
        log_write(LOG_ERROR, __FILE__, __func__, __LINE__, "SQL error (delete data): %s", err_msg);
        //sqlite3_free(err_msg);
		//sqlite3_free(sql);  //释放内存
        return rc;
    }

    log_write(LOG_INFO, __FILE__, __func__, __LINE__, "Record deleted successfully.");

    return SQLITE_OK;
}

//回调函数，用于查询时打印结果
int callback(void *data,int argc,char **argv,char **col_time)
{
	 char *buf = (char *)data;

    for (int i = 0; i < argc; i++) 
	{
		if (argv[i]) 
		{
			// 直接将 raw_data 数据添加到 buf 中
            strcat(buf, argv[i]);
            //strcat(buf, "11112222\n");  // 每条记录后换行
        }
    }
	return 0;
}
